using System;
using Oracle.DataAccess.Client;
using System.Data;

namespace ISHotel.Database
{
    public class Database
    {
        // private static int TIMEOUT = 240;
        private OracleConnection mConnection = new OracleConnection();
        uint mConnectNumber = 0;
        bool mTransactionFlag = false;
        OracleTransaction mSqlTransaction = null;
        private String mLanguage = "en";
        private static String CONNECTION_STRING = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dbedu.cs.vsb.cz)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=Oracle)));User Id=bis026;Password=feifei;";

        public HostTable HostTable;
        public ObjednavkaPokojeTable ObjednavkaPokojeTable;
        public ObjednavkaSluzbyTable ObjednavkaSluzbyTable;
        public ObjednavkaStravovaniTable ObjednavkaStravovaniTable;
        public PobytTable PobytTable;
        public PokojTable PokojTable;
        public SeznamHostuTable SeznamHostuTable;
        public SluzbaTable SluzbaTable;
        public StravovaniTable StravovaniTable;
        public TaxaTable TaxaTable;


        public Database()
        {
            DbTable.SetDatabase(this);
            mConnection = new OracleConnection();

            HostTable = new HostTable();
            ObjednavkaPokojeTable = new ObjednavkaPokojeTable();
            ObjednavkaSluzbyTable = new ObjednavkaSluzbyTable();
            ObjednavkaStravovaniTable = new ObjednavkaStravovaniTable();
            PobytTable = new PobytTable();
            PokojTable = new PokojTable();
            SeznamHostuTable = new SeznamHostuTable();
            SluzbaTable = new SluzbaTable();
            StravovaniTable = new StravovaniTable();
            TaxaTable = new TaxaTable();
        }

        /**
         * Connect.
         **/
        public bool Connect(String conString)
        {
            if (!mTransactionFlag)
            {
                mConnectNumber++;
            }
            if (mConnection.State != System.Data.ConnectionState.Open)
            {
                mConnection.ConnectionString = conString;
                mConnection.Open();
            }
            return true;
        }

        /**
         * Connect.
         **/
        public bool Connect()
        {
            bool ret = true;

            if (mConnection.State != System.Data.ConnectionState.Open)
            {
                ret = Connect(CONNECTION_STRING);
                // ret = Connect(WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            }
            else if (!mTransactionFlag)
            {
                mConnectNumber++;
            }
            return ret;
        }

        /**
         * Close.
         **/
        public bool Close()
        {
            if (!mTransactionFlag)
            {
                if (mConnectNumber > 0)
                {
                    mConnectNumber--;
                }
            }

            if (mConnectNumber == 0)
            {
                mConnection.Close();
            }
            return true;
        }

        /**
         * Begin a transaction.
         **/
        public void BeginTransaction()
        {
            
            mSqlTransaction = mConnection.BeginTransaction(IsolationLevel.Serializable);

            mTransactionFlag = true;
        }

        /**
         * End a transaction.
         **/
        public void EndTransaction()
        {
            // command.Dispose()
            mSqlTransaction.Commit();
            mTransactionFlag = false;
            mConnection.Close();
            Close();
        }

        /**
         * If a transaction is failed call it.
         **/
        public void Rollback()
        {
            mSqlTransaction.Rollback();
        }

        /**
         * Update a record encapulated in the command.
         **/
        public int Update(OracleCommand command)
        {
            int rowNumber = 0;
            try
            {
                command.Prepare();
                rowNumber = command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                Close();
            }
            return rowNumber;
        }

        /**
         * Insert a record encapulated in the command.
         **/
        public int Insert(OracleCommand command)
        {
            int rowNumber = 0;
            try
            {
                command.Prepare();
                rowNumber = command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                
                throw e;
            }
            finally
            {
                Close();
            }
            return rowNumber;
        }






        /**
         * Create command.
         **/
        public OracleCommand CreateCommand(string strCommand)
        {
            OracleCommand command = new OracleCommand(strCommand, mConnection);

            if (mTransactionFlag)
            {
                command.Transaction = mSqlTransaction;
            }
            return command;
        }

        /**
         * Select encapulated in the command.
         **/
        public OracleDataReader Select(OracleCommand command)
        {
            command.Prepare();
            OracleDataReader sqlReader = command.ExecuteReader();
            return sqlReader;
        }

        /**
         * Delete encapulated in the command.
         **/
        public int Delete(OracleCommand command)
        {
            int rowNumber = 0;
            try
            {
                command.Prepare();
                rowNumber = command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                Close();
            }
            return rowNumber;
        }

        public String Language
        {
            get
            {
                return mLanguage;
            }
            set
            {
                mLanguage = value;
            }
        }
    }
}

